package com.arong.swing.db.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.arong.swing.db.JDBCUtils;
import com.arong.swing.db.entity.Staff;
import com.arong.swing.exception.AppException;
import com.arong.swing.exception.SysException;
public class StaffSDAO  extends BaseDAO {
	public StaffSDAO(){
		this(DB_ANRONGTEC);
	}
	
	public StaffSDAO(String dbFile){
		super(dbFile);
	}
	public StaffSDAO(JDBCUtils jdbcUtils){
		super(jdbcUtils);
	}
	public String getCreateTableSql(){
		return "CREATE TABLE IF NOT EXISTS "+
				"STAFF("+
				  "STAFF_ID        NUMBER(12) NOT NULL,"+
				  "LOGIN_NAME      VARCHAR2(64) NOT NULL,"+
				  "LOGIN_PASSWORD  VARCHAR2(64) NOT NULL,"+
				  "DEPT_ID         NUMBER(12) NOT NULL,"+
				  "STAFF_NAME      VARCHAR2(32) NOT NULL,"+
				  "STAFF_CODE      VARCHAR2(64) NOT NULL,"+
				  "SEX             CHAR(1),"+
				  "PHOTO           BLOB,"+
				  "DEPARTMENTS     VARCHAR2(32),"+
				  "DUTY            VARCHAR2(64),"+
				  "JINGXIAN       VARCHAR2(32),"+
				  "JINGZHONG       VARCHAR2(32),"+
				  "ORIGINAL       VARCHAR2(64),"+
				  "NATION           VARCHAR2(32),"+
				  "TELEPHONE       VARCHAR2(32),"+
				  "EMAIL           VARCHAR2(32),"+
				  "ADDRESS         VARCHAR2(64),"+
				  "REMARK      VARCHAR2(256),"+
				  "STS             CHAR(1) NOT NULL,"+
				  "STS_TIME        DATE NOT NULL,"+
				  "CREATE_TIME     DATE NOT NULL,"+
				  "CREATE_STAFF_ID NUMBER(12) NOT NULL"+
				")";
		
	}
	/**
	 * 插入
	 * @param entity
	 * @return
	 * @throws SysException
	 */
	public  int insert(Staff entity) throws SysException,AppException {
		int i=0;
		try {
		 	i= jdbcUtils.insert(entity);
		} catch (SQLException e) {
			throw new SysException("add error", e);
		}
		return i;
	}

	/**
	 * 修改
	 * @param entity
	 * @param keyPropName
	 * @return
	 * @throws SysException
	 */
	public  int update(Staff entity) throws SysException,AppException {
		int i=0;
		try {
			i=jdbcUtils.update(entity, "staffId");
		} catch (SQLException e) {
			throw new SysException("update error",e);
		}
		return i;
	}
	/**
	 * 删除
	 * @param entity
	 * @param keyPropName
	 * @return
	 * @throws SysException
	 */
	public  int delete(Staff entity) throws SysException,AppException {
		int i=0;
		try {
			jdbcUtils.execute("DELETE FROM staff where staff_id="+entity.getStaffId());
		} catch (SQLException e) {
			throw new SysException("update error",e);
		}
		return i;
	}


	/**
	 * 查询 集合
	 * @param sql
	 * @param clazz
	 * @param params
	 * @return
	 * @throws SysException
	 */
	public   List<Staff> queryList(Staff entity) throws SysException,AppException {
	    List<Staff> res=null;
	    try {
			res= jdbcUtils.queryList(getSql(entity), Staff.class,
			getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryList集合查询 error",e);
		}
		return   res;
	}
	/**
	 * 查询对象
	 * @param sql
	 * @param clazz
	 * @param params
	 * @return
	 * @throws SysException
	 */
	public  Staff queryBean(Staff entity) throws SysException,AppException {
		Staff res=null;
	    try {
			res=  jdbcUtils.queryBean(getSql(entity), Staff.class,
				getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryBean对象查询 error",e);
		}
		return   res;
	}
	public  Staff queryBeanByName(String name) throws SysException,AppException {
		Staff res=null;
	    try {
	    	Object[] params = {name,name};
	    	StringBuffer sql = new StringBuffer("SELECT ");
			sql.append(" a.STAFF_ID,a.STAFF_NAME,a.LOGIN_NAME,a.LOGIN_PASSWORD,a.DEPT_ID,a.TELEPHONE,a.EMAIL,a.STS,a.STS_TIME,a.CREATE_TIME,a.CREATE_STAFF_ID,a.STAFF_CODE");
			sql.append(" from STAFF a where (a.LOGIN_NAME=? or a.STAFF_CODE=?)");
			res=  jdbcUtils.queryBean(sql.toString(), Staff.class,params);
		} catch (SQLException e) {
			throw new SysException("queryBean对象查询 error",e);
		}
		return   res;
	}
	/**
	 * 获取查询条件
	 * @return
	 */
	private Object[] getParams(Staff staff) {
		List<Object> qryList = new ArrayList<Object>();
		if (staff.getStaffId() != null) {
			qryList.add(staff.getStaffId());
		}
		
		if (staff.getStaffName() != null) {
			qryList.add(staff.getStaffName());
		}
		if (staff.getLoginName() != null) {
			qryList.add(staff.getLoginName());
		}
		if (staff.getLoginPassword() != null) {
			qryList.add(staff.getLoginPassword());
		}
		if (staff.getDeptId() != null) {
			qryList.add(staff.getDeptId());
		}
		if (staff.getTelephone() != null) {
			qryList.add(staff.getTelephone());
		}
		if (staff.getEmail() != null) {
			qryList.add(staff.getEmail());
		}
		if (staff.getSts() != null) {
			qryList.add(staff.getSts());
		}
		if (staff.getStsTime() != null) {
			qryList.add(staff.getStsTime());
		}
		if (staff.getCreateTime() != null) {
			qryList.add(staff.getCreateTime());
		}
		if (staff.getCreateStaffId() != null) {
			qryList.add(staff.getCreateStaffId());
		}
		if (staff.getStaffCode() != null) {
			qryList.add(staff.getStaffCode());
		}
		return super.setQryList(qryList);
	}

	/**
	 * 获取sql
	 * @return
	 */
	private String getSql(Staff staff) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" a.STAFF_ID,a.STAFF_NAME,a.LOGIN_NAME,a.LOGIN_PASSWORD,a.DEPT_ID,a.TELEPHONE,a.EMAIL,a.STS,a.STS_TIME,a.CREATE_TIME,a.CREATE_STAFF_ID,a.STAFF_CODE");
		sql.append(" from STAFF a where 1=1");
		if (staff.getStaffId() != null) {
			sql.append(" and STAFF_ID=?");
		}
		
		if (staff.getStaffName() != null) {
			sql.append(" and STAFF_NAME=?");
		}
		if (staff.getLoginName() != null) {
			sql.append(" and LOGIN_NAME=?");
		}
		if (staff.getLoginPassword() != null) {
			sql.append(" and LOGIN_PASSWORD=?");
		}
		if (staff.getDeptId() != null) {
			sql.append(" and DEPT_ID=?");
		}
		if (staff.getTelephone() != null) {
			sql.append(" and TELEPHONE=?");
		}
		if (staff.getEmail() != null) {
			sql.append(" and EMAIL=?");
		}
		if (staff.getSts() != null) {
			sql.append(" and STS=?");
		}
		if (staff.getStsTime() != null) {
			sql.append(" and STS_TIME=?");
		}
		if (staff.getCreateTime() != null) {
			sql.append(" and CREATE_TIME=?");
		}
		if (staff.getCreateStaffId() != null) {
			sql.append(" and CREATE_STAFF_ID=?");
		}
		if (staff.getStaffCode() != null) {
			sql.append(" and STAFF_CODE=?");
		}
		return sql.toString();
	}
}

